package com.example.demo.model.component;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.example.demo.common.QueryParameter;
import com.example.demo.common.TestSql;
import com.example.demo.config.CommonConfiguration;
import com.example.demo.config.MySQLConfiguration;
import com.example.demo.model.mapper.CommonMapper;
import com.example.demo.student.entity.Student;
import com.example.demo.student.service.IStudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

/**
 * @author yuanz
 */
@Component
public class MySQLComponent {

    @Resource
    private CommonMapper commonMapper;

    @Autowired
    private MySQLConfiguration mySQLConfiguration;

    @Autowired
    private CommonConfiguration commonConfiguration;

//    @Autowired
//    private IStudentService studentService;
//
//    public List<Student> getStudents() {
//        LambdaQueryWrapper<Student> query = Wrappers.lambdaQuery(Student.class);
//        return studentService.list(query);
//    }
//
//    public int saveStudents(Student student) {
//        return studentService.saveStudent(student);
//    }

    public String testInsert() {
        long start = System.currentTimeMillis();
        String sql = "insert into yuan_test ( id, name , create_time) values ('idVal' , 'nameVal' , now())";
        int id = 1, size = commonConfiguration.totalSize;
        String namePrefix = "单条入库工程师-";
        for (int i = 0; i < size; i++) {
            String exeSql = sql.replace("idVal", String.valueOf(id)).replace("nameVal", namePrefix + id);
            commonMapper.executeSql(exeSql);
            id++;
        }
        long end = System.currentTimeMillis();
        long use = end - start;
        return "MySQL数据库入库:yuan_test-" + size + "条数据总耗时：" + use + "毫秒。";
    }

    public String testInsertByBatch() {
        int batchSize = commonConfiguration.batchSize;
        int batchNum = commonConfiguration.batchNum;
        long start = System.currentTimeMillis();
        try {
            Class.forName(mySQLConfiguration.driverClassName);
            Connection conn = DriverManager.getConnection(mySQLConfiguration.url, mySQLConfiguration.username, mySQLConfiguration.password);
            String sql = "insert into yuan_test (id,name,create_time) values (?,?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            System.out.println("Opened database successfully of testInsertByBatch!");
            long id = 1;
            String namePrefix = "批量入库工程师-";
            for (int i = 0; i < batchNum; i++) {
                for (int j = 0; j < batchSize; j++) {
                    ps.setLong(1, id);
                    ps.setString(2, namePrefix + id);
                    ps.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
                    ps.addBatch();
                    id++;
                }
                ps.executeBatch();
                conn.commit();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        long end = System.currentTimeMillis();
        long use = end - start;
        return "MySQL数据库批量入库" + (batchSize * batchNum) + "条数据总耗时：" + use + "毫秒。";
    }

    public String testQuery(QueryParameter queryParameter) {
        // 分页查询参数
        int curPage = queryParameter.getCurPage(), pageSize = queryParameter.getPageSize();
        String tableName = queryParameter.getTableName();
//        QueryParameter queryParameter = new QueryParameter();
//        queryParameter.setTableName(tableName);
//        queryParameter.setCurPage(curPage);
//        queryParameter.setPageSize(pageSize);
        queryParameter.setSortName("create_time");
        queryParameter.setSortOrder("desc");
        long start = System.currentTimeMillis();
        List<Map<Object, Object>> data;
        int pageSizeQuery;
        while (true) {
            System.out.println(String.format("当前查询第[ %s ]页的[ %s ]条数据", curPage, pageSize));
            long sc1 = System.currentTimeMillis();
            data = commonMapper.testQueryMySql(queryParameter);
            long sc2 = System.currentTimeMillis();
            System.out.println(String.format("当前第[ %s ]页的[ %s ]条数据标准化耗时[ %s ]毫秒", curPage, pageSize, sc2 - sc1));
            // 跳出循环
            int querySize = data.size();
            pageSizeQuery = querySize;
            if (querySize < pageSize) {
                break;
            }
            // 增加当前页
            queryParameter.setCurPage(++curPage);
        }
        long end = System.currentTimeMillis();
        long use = end - start;
        return "MySQL数据库查询:" + tableName + "-" + (pageSize * (curPage - 1) + pageSizeQuery) + "条数据总耗时：" + use + "毫秒。";
    }

    public String testDelete() {
        long start = System.currentTimeMillis();
        String sql = "delete from yuan_test where id = idVal";
        int id = 1, size = commonConfiguration.totalSize;
        for (int i = 0; i < size; i++) {
            String exeSql = sql.replace("idVal", String.valueOf(id));
            commonMapper.executeSql(exeSql);
            id++;
        }
        long end = System.currentTimeMillis();
        long use = end - start;
        return "MySQL数据库删除:yuan_test-" + size + "条数据总耗时：" + use + "毫秒。";
    }

    public String testUpdate() {
        long start = System.currentTimeMillis();
        String sql = "UPDATE yuan_test set name = 'nameVal'  WHERE id = idVal";
        int id = 1, size = commonConfiguration.totalSize;
        String namePrefix = "单条更新项目经理-";
        for (int i = 0; i < size; i++) {
            String exeSql = sql.replace("idVal", String.valueOf(id)).replace("nameVal", namePrefix + id);
            commonMapper.executeSql(exeSql);
            id++;
        }
        long end = System.currentTimeMillis();
        long use = end - start;
        return "MySQL数据库更新:yuan_test-" + size + "条数据总耗时：" + use + "毫秒。";
    }

    public String testUpdateByBatch() {
        int batchSize = commonConfiguration.batchSize;
        int batchNum = commonConfiguration.batchNum;
        long start = System.currentTimeMillis();
        try {
            Class.forName(mySQLConfiguration.driverClassName);
            Connection conn = DriverManager.getConnection(mySQLConfiguration.url, mySQLConfiguration.username, mySQLConfiguration.password);
            String sql = "UPDATE yuan_test set name = ? WHERE id = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            System.out.println("Opened database successfully of testUpdateByBatch!");
            long id = 1;
            String namePrefix = "批量更新DBA工程师-";
            for (int i = 0; i < batchNum; i++) {
                for (int j = 0; j < batchSize; j++) {
                    ps.setString(1, namePrefix + id);
                    ps.setLong(2, id);
                    ps.addBatch();
                    id++;
                }
                ps.executeBatch();
                conn.commit();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        long end = System.currentTimeMillis();
        long use = end - start;
        return "MySQL数据库批量更新" + (batchSize * batchNum) + "条数据总耗时：" + use + "毫秒。";
    }

    public String testDeleteByBatch() {
        int batchSize = commonConfiguration.batchSize;
        int batchNum = commonConfiguration.batchNum;
        long start = System.currentTimeMillis();
        try {
            Class.forName(mySQLConfiguration.driverClassName);
            Connection conn = DriverManager.getConnection(mySQLConfiguration.url, mySQLConfiguration.username, mySQLConfiguration.password);
            String sql = "delete from yuan_test where id = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            System.out.println("Opened database successfully of testDeleteByBatch!");
            long id = 1;
            for (int i = 0; i < batchNum; i++) {
                for (int j = 0; j < batchSize; j++) {
                    ps.setLong(1, id);
                    ps.addBatch();
                    id++;
                }
                ps.executeBatch();
                conn.commit();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        long end = System.currentTimeMillis();
        long use = end - start;
        return "MySQL数据库批量删除" + (batchSize * batchNum) + "条数据总耗时：" + use + "毫秒。";
    }

    public String testSql(TestSql testSql) {
        String sql = testSql.getSql();
        long start = System.currentTimeMillis();
        List<Map<Object, Object>> mapList = commonMapper.executeQuerySql(sql);
        long end = System.currentTimeMillis();
        long use = end - start;
        return "MySQL执行SQL总耗时：" + use + "毫秒；" + "查询结果条数：" + mapList.size() + "。";
    }
}
